
#region Imported Namespaces
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Threading;
#endregion

namespace PHSRAG.Utility
{
	#region Class DataAccess
	/// <summary>
	/// The DataAccess class provides exception-safe wrappers around the ADO.NET reader/dataset interface.
	/// This does not mean that method don't throw exceptions; it simply means that even in the face of an
	/// exception, resources are handled correctly. For example, any open database connection will be closed
	/// even under exceptions. It is the responsibility of the caller to catch the exception that is
	/// propagated/thrown by methods within this class.
	/// </summary>
	public class DataAccess
	{
		#region Private Types
		enum ExecuteType { ExecuteReader, ExecuteScalar, ExecuteNonQuery }
		#endregion

		#region Public Types
		/// <summary>
		/// The Connection class stores the minimal characteristics of a database connection (login/password/server/catalog).
		/// </summary>
		public class Connection
		{
			#region Instance Variables
			private string catalog;
			private string connectionString;
			private string password;
			private string server;
			private string userID;
			private string connectionPoolSize;
			#endregion

			#region Public Properties
			/// <summary>Get the catalog associated with this connection</summary>
			public string Catalog { get { return catalog; } }

			/// <summary>Get the connection string associated with this connection</summary>
			public string ConnectionString { get { return connectionString; } }

			/// <summary>Get the password associated with this connection</summary>
			public string Password { get { return password; } }

			/// <summary>Get the server associated with this connection</summary>
			public string Server { get { return server; } }

			/// <summary>Get the userID associated with this connection</summary>
			public string UserID { get { return userID; } }

			/// <summary>Get the connectionPoolSize associated with this DB connection</summary>
			public string ConnectionPoolSize { get { return connectionPoolSize; } }
			#endregion

			/// <summary>
			/// Initialize the Connection object with the specified characteristics.
			/// </summary>
			/// <param name="userID">Database login</param>
			/// <param name="password">Database password</param>
			/// <param name="server">Database server</param>
			/// <param name="catalog">Database instance</param>
			/// <param name="connectionPoolSize">Database connection Pool size</param>
			/// <param name="options">Any additional connection options</param>
			public Connection(string userID, string password, string server, string catalog, string connectionPoolSize, string options)
			{
				connectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Max Pool Size={4};{5}",
					this.server = server, this.catalog = catalog, this.userID = userID, this.password = password, this.connectionPoolSize=connectionPoolSize, options);
			}
		}
		#endregion

		#region Class Variables
		private static Hashtable	connections = InitializeConnections();
		private static string		defaultConnection;
		#endregion

		#region Public Properties
		/// <summary>Get the database connection string</summary>
		public static string ConnectionString	{ get { return GetConnectionString(); } }
		#endregion

        public static Hashtable InitializeConnections()
        {
            Hashtable connections = new Hashtable();
            string connectionList = Settings.GetKeyValue("Database.Connections", string.Empty);
            if (!String.IsNullOrEmpty(connectionList))
            {
                foreach (string key in connectionList.Trim().Split(','))
                {
                    if (defaultConnection == null)
                        defaultConnection = key;

                    connections[key] = new Connection(
                        Settings.GetKeyValue(string.Format("Database.{0}.UserID", key), string.Empty),
                        Crypt.DecryptFromString(Settings.GetKeyValue(string.Format("Database.{0}.Password", key), string.Empty)),
                        Settings.GetKeyValue(string.Format("Database.{0}.Server", key), string.Empty),
                        Settings.GetKeyValue(string.Format("Database.{0}.Catalog", key), string.Empty),
                        Settings.GetKeyValue(string.Format("Database.{0}.ConnectionPoolSize", key), string.Empty),
                        Settings.GetKeyValue(string.Format("Database.{0}.Options", key), string.Empty));
                }
            }
            return connections;
        }

		#region Public Methods
		/// <summary>
		/// Appends to a pre-build array of SqlParameter objects given a set of name/type/value triplet for
		/// each argument. It is crucial that each argument be specified using the triplet. A check is made
		/// very early in the method to ensure that the number of arguments provided is evenly divisible
		/// by three, failing which an exception is thrown.
		/// This method is typically invoked after invoking the BuildSqlParameterArray() method.
		/// </summary>
		/// <param name="args">Pre-build array of SqlParameter objects</param>
		/// <param name="nameTypeValue">A comma-separated list of name/type/value (one per parameter)</param>
		public static void AppendToSqlParameterArray(ref SqlParameter[] args, params object[] nameTypeValue)
		{
			if ((nameTypeValue.Length % 3) != 0)
				throw new Exception("Name/Type/Value is unbalanced");

			int originalArgCount = (args == null) ? 0 : args.Length;
			if (originalArgCount == 0)
				args = new SqlParameter[nameTypeValue.Length / 3];

			int argCount = originalArgCount + (nameTypeValue.Length / 3);
			SqlParameter[] temp = new SqlParameter[argCount];
			args.CopyTo(temp, 0);
			args = temp;

			for (int arg = originalArgCount, ntv = 0; arg < argCount; ++arg, ntv += 3)
			{
				args[arg] = new SqlParameter((string)nameTypeValue[ntv], (SqlDbType)nameTypeValue[ntv + 1]);
				args[arg].Value = nameTypeValue[ntv + 2];
			}
		}

		/// <summary>
		/// This method builds an array of SqlParameter objects given a set of name/type/value triplet for
		/// each argument. It is crucial that each argument be specified using the triplet. A check is made
		/// very early in the method to ensure that the number of arguments provided is evenly divisible
		/// by three, failing which an exception is thrown.
		/// 
		/// An example invocation may looks liks this:
		/// SqlParameter[] args = BuildSqlParameterArray("@firstName", SqlDbType.Varchar, "Bihari");
		/// 
		/// </summary>
		/// <param name="nameTypeValue">A comma-separated list of name/type/value (one per parameter)</param>
		/// <returns>An array of initialized SqlParameter objects</returns>
		public static SqlParameter[] BuildSqlParameterArray(params object[] nameTypeValue)
		{
			if ((nameTypeValue.Length % 3) != 0)
				throw new Exception("Name/Type/Value is unbalanced");

			int argCount = nameTypeValue.Length / 3;
			SqlParameter[] args = new SqlParameter[argCount];
			for (int arg = 0, ntv = 0; arg < argCount; ++arg, ntv += 3)
			{
				args[arg] = new SqlParameter((string)nameTypeValue[ntv], (SqlDbType)nameTypeValue[ntv + 1]);
				args[arg].Value = nameTypeValue[ntv + 2];
			}

			return args;
		}

		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void ExecuteNonQuery(string connectionString, string storedProcedureName, params SqlParameter[] args)
		{
			Execute(ExecuteType.ExecuteNonQuery, connectionString, storedProcedureName, args);
		}
		
		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void ExecuteNonQuery(SqlConnection connection, string storedProcedureName, params SqlParameter[] args)
		{
			Execute(ExecuteType.ExecuteNonQuery, connection, null, storedProcedureName, args);
		}

		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="transaction">Transaction associated with the open connection</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string storedProcedureName, params SqlParameter[] args)
		{
			Execute(ExecuteType.ExecuteNonQuery, connection, transaction, storedProcedureName, args);
		}

		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static SqlDataReader ExecuteReader(string connectionString, string storedProcedureName, params SqlParameter[] args)
		{
			return (SqlDataReader)Execute(ExecuteType.ExecuteReader, connectionString, storedProcedureName, args);
		}

		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static SqlDataReader ExecuteReader(SqlConnection connection, string storedProcedureName, params SqlParameter[] args)
		{
			return (SqlDataReader)Execute(ExecuteType.ExecuteReader, connection, null, storedProcedureName, args);
		}

		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="transaction">Transaction associated with the open connection</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, string storedProcedureName, params SqlParameter[] args)
		{
			return (SqlDataReader)Execute(ExecuteType.ExecuteReader, connection, transaction, storedProcedureName, args);
		}

		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static object ExecuteScalar(string connectionString, string storedProcedureName, params SqlParameter[] args)
		{
			return Execute(ExecuteType.ExecuteScalar, connectionString, storedProcedureName, args);
		}

		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static object ExecuteScalar(SqlConnection connection, string storedProcedureName, params SqlParameter[] args)
		{
			return Execute(ExecuteType.ExecuteScalar, connection, null, storedProcedureName, args);
		}
		
		/// <summary>
		/// Simple wrapper around like-named ADO.NET method.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="transaction">Transaction associated with the open connection</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, string storedProcedureName, params SqlParameter[] args)
		{
			return Execute(ExecuteType.ExecuteScalar, connection, transaction, storedProcedureName, args);
		}

		/// <summary>
		/// This method flushes the procedure cache maintained by SQL Server. A potential use of this method is in the
		/// context of gathering timing information for stored procedures and we don't want that data to be skewed by
		/// any existing cache contents.
		/// </summary>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		public static void FlushProcedureCache(string connectionString)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			try
			{
				connection.Open();
				FlushProcedureCache(connection);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
					connection.Close();
			}
		}

		/// <summary>
		/// This method flushes the procedure cache maintained by SQL Server. A potential use of this method is in the
		/// context of gathering timing information for stored procedures and we don't want that data to be skewed by
		/// any existing cache contents.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		public static void FlushProcedureCache(SqlConnection connection)
		{
			(new SqlCommand("dbcc freeproccache", connection)).ExecuteNonQuery();
		}

		/// <summary>
		/// </summary>
		/// <param name="args">Optional name of the connection</param>
		/// <returns>SQL Server compatible connection string</returns>
		public static Connection GetConnection(params string[] args)
		{
			return ((args.Length == 0) ? connections[defaultConnection] : connections[args[0]]) as Connection;
		}

		/// <summary>
		/// This method returns a fully-formed connection string geared towards SQL Server access. Elements of such
		/// connections string (user id, password, etc.) are stored in an application configuration file, and the
		/// password is expected to be stored encrypted. This method gets those constituent elements, decrypts
		/// the password and forms the connection string.
		/// </summary>
		/// <param name="args">Optional name of the connection</param>
		/// <returns>SQL Server compatible connection string</returns>
		public static string GetConnectionString(params string[] args)
		{
            Hashtable ConnectionSource = Thread.GetData(Thread.GetNamedDataSlot("Connections")) as Hashtable;
            if (null == ConnectionSource)
            {
                Connection connection = GetConnection(args);
                return (connection == null) ? null : connection.ConnectionString;
            }
            else
            {		//The following part will be used by skelta to instantiate the insight application in Skelta context			
                string connectionString = null;
                if (args.Length == 0)
                    connectionString = (!ConnectionSource.ContainsKey(defaultConnection == null ? "Insight" : defaultConnection) || String.IsNullOrEmpty(ConnectionSource[defaultConnection == null ? "Insight" : defaultConnection] as string)) ? null : ConnectionSource[defaultConnection == null ? "Insight" : defaultConnection] as string;
                else if (args.Length == 1)
                {
                    
                    connectionString = (!ConnectionSource.ContainsKey(args[0]) || String.IsNullOrEmpty(ConnectionSource[args[0]] as string)) ?
                        GetConnection(args).ConnectionString : ConnectionSource[args[0]] as string;
                }
                return connectionString;
            }
		}
        
		/// <summary>
		/// Return a DataSet populated by executing the specified stored procedure.
		/// </summary>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static DataSet GetDataSet(string connectionString, string storedProcedureName, params SqlParameter[] args)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			try
			{
				connection.Open();
				return GetDataSet(connection, storedProcedureName, args);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
					connection.Close();
			}
		}

		/// <summary>
		/// Return a DataSet populated by executing the specified stored procedure.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static DataSet GetDataSet(SqlConnection connection, string storedProcedureName, params SqlParameter[] args)
		{
			return GetDataSet(connection, null, storedProcedureName, args);
		}
		
		/// <summary>
		/// Return a DataSet populated by executing the specified stored procedure.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="transaction">Transaction (if any) associated with the open connection</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static DataSet GetDataSet(SqlConnection connection, SqlTransaction transaction, string storedProcedureName, params SqlParameter[] args)
		{
			if (connection.State != ConnectionState.Open)
				throw new Exception("Connection is not open");

			try
			{
				SqlCommand command = new SqlCommand();
				command.Connection = connection;
				command.CommandType = CommandType.StoredProcedure;
				command.CommandText = storedProcedureName;
				command.CommandTimeout = Settings.GetKeyValue("Database.CommandTimeout", 0);
				if (transaction != null)
					command.Transaction = transaction;

				if ((args != null) && (args.Length > 0))
					foreach (SqlParameter p in args)
						command.Parameters.Add(p);
			
				DataSet ds = new DataSet();
				SqlDataAdapter adapter = new SqlDataAdapter(command);
				adapter.Fill(ds);

				return ds;
			}
			catch (Exception e)
			{
				throw new Exception(String.Format("Failed to execute {0}", storedProcedureName), e);
			}
		}

		/// <summary>
		/// Return a loaded DataTable by executing the specified stored procedure.
		/// </summary>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		/// <returns>Loaded DataTable</returns>
		public static DataTable GetDataTable(string connectionString, string storedProcedureName, params SqlParameter[] args)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			try
			{
				connection.Open();
				return GetDataTable(connection, storedProcedureName, args);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
					connection.Close();
			}
		}

		/// <summary>
		/// Return a loaded DataTable by executing the specified stored procedure.
		/// </summary>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		/// <returns>Loaded DataTable</returns>
		public static DataTable GetDataTable(SqlConnection connection, string storedProcedureName, params SqlParameter[] args)
		{
			if (connection.State != ConnectionState.Open)
				throw new Exception("Connection is not open");

			try
			{
				SqlCommand command = new SqlCommand();
				command.Connection = connection;
				command.CommandType = CommandType.StoredProcedure;
				command.CommandText = storedProcedureName;
				command.CommandTimeout = Settings.GetKeyValue("Database.CommandTimeout", 0);

				if ((args != null) && (args.Length > 0))
					foreach (SqlParameter p in args)
						command.Parameters.Add(p);

				DataTable dt = new DataTable();
				(new SqlDataAdapter(command)).Fill(dt);
				return dt;
			}
			catch (Exception e)
			{
				throw new Exception(String.Format("Failed to execute {0}", storedProcedureName), e);
			}
		}

		/// <summary>
		/// Load the specified DataSet by executing the specified stored procedure. Note that the load occurs
		/// within the DataTable whose name is specified. This is useful in loading a single DataSet with
		/// multiple DataTables.
		/// </summary>
		/// <param name="ds">Instance of the DataSet to load</param>
		/// <param name="tableName">Name of the DataTable within the DataSet to load into</param>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void LoadDataSet(DataSet ds, string tableName, string connectionString,
			string storedProcedureName, params SqlParameter[] args)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			try
			{
				connection.Open();
				LoadDataSet(ds, tableName, connection, storedProcedureName, args);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
					connection.Close();
			}
		}

		/// <summary>
		/// Load the specified DataSet by executing the specified stored procedure. Note that the load occurs
		/// within the DataTable whose name is specified. This is useful in loading a single DataSet with
		/// multiple DataTables.
		/// </summary>
		/// <param name="ds">Instance of the DataSet to load</param>
		/// <param name="tableName">Name of the DataTable within the DataSet to load into</param>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void LoadDataSet(DataSet ds, string tableName, SqlConnection connection,
			string storedProcedureName, params SqlParameter[] args)
		{
			if (connection.State != ConnectionState.Open)
				throw new Exception("Connection is not open");

			try
			{
				SqlCommand command = new SqlCommand();
				command.Connection = connection;
				command.CommandType = CommandType.StoredProcedure;
				command.CommandText = storedProcedureName;
				command.CommandTimeout = Settings.GetKeyValue("Database.CommandTimeout", 0);

				if ((args != null) && (args.Length > 0))
					foreach (SqlParameter p in args)
						command.Parameters.Add(p);

				SqlDataAdapter adapter = new SqlDataAdapter(command);
				if (tableName != null)
					adapter.Fill(ds, tableName);
				else
					adapter.Fill(ds);
			}
			catch (Exception e)
			{
				throw new Exception(String.Format("Failed to execute {0}", storedProcedureName), e);
			}
		}

		/// <summary>
		/// Load the specified DataTable by executing the specified stored procedure.
		/// </summary>
		/// <param name="dt">DataTable to load</param>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void LoadDataTable(DataTable dt, string connectionString,	string storedProcedureName,
			params SqlParameter[] args)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			try
			{
				connection.Open();
				LoadDataTable(dt, connection, storedProcedureName, args);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
					connection.Close();
			}
		}

		/// <summary>
		/// Load the specified DataTable by executing the specified stored procedure.
		/// </summary>
		/// <param name="dt">DataTable to load</param>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void LoadDataTable(DataTable dt, SqlConnection connection, string storedProcedureName,
			params SqlParameter[] args)
		{
			if (connection.State != ConnectionState.Open)
				throw new Exception("Connection is not open");

			try
			{
				SqlCommand command = new SqlCommand();
				command.Connection = connection;
				command.CommandType = CommandType.StoredProcedure;
				command.CommandText = storedProcedureName;
				command.CommandTimeout = Settings.GetKeyValue("Database.CommandTimeout", 0);

				if ((args != null) && (args.Length > 0))
					foreach (SqlParameter p in args)
						command.Parameters.Add(p);

				(new SqlDataAdapter(command)).Fill(dt);
			}
			catch (Exception e)
			{
				throw new Exception(String.Format("Failed to execute {0}", storedProcedureName), e);
			}
		}

		/// <summary>
		/// Load the specified typed DataSet by executing the specified stored procedure. Note that the load occurs
		/// within the DataTable list whose names are retrieved from the dataset schema. This is useful in loading a single DataSet with
		/// multiple DataTables.
		/// </summary>
		/// <param name="ds">Instance of the DataSet to load</param>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void LoadTypedDataSet(DataSet ds, string connectionString, string storedProcedureName, params SqlParameter[] args)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			try
			{
				connection.Open();
				LoadTypedDataSet(ds, connection, storedProcedureName, args);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
					connection.Close();
			}
		}

		/// <summary>
		/// Load the specified typed DataSet by executing the specified stored procedure. Note that the load occurs
		/// within the DataTable list whose names are retrieved from the dataset schema. This is useful in loading a single DataSet with
		/// multiple DataTables.
		/// </summary>
		/// <param name="ds">Instance of the DataSet to load</param>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		public static void LoadTypedDataSet(DataSet ds, SqlConnection connection, string storedProcedureName, params SqlParameter[] args)
		{
			if (connection.State != ConnectionState.Open)
				throw new Exception("Connection is not open");

			try
			{
				SqlCommand command = new SqlCommand();
				command.Connection = connection;
				command.CommandType = CommandType.StoredProcedure;
				command.CommandText = storedProcedureName;
				command.CommandTimeout = Settings.GetKeyValue("Database.CommandTimeout", 0);

				if ((args != null) && (args.Length > 0))
					foreach (SqlParameter p in args)
						command.Parameters.Add(p);

				SqlDataAdapter adapter = new SqlDataAdapter(command);
				for (int i = 0, count = ds.Tables.Count; i < count; ++i)
					adapter.TableMappings.Add(string.Format("Table{0}", (i == 0) ? string.Empty : i.ToString()), ds.Tables[i].TableName);

				adapter.Fill(ds);
			}
			catch (Exception e)
			{
				throw new Exception(String.Format("Failed to execute {0}", storedProcedureName), e);
			}
		}

		/// <summary>
		/// This methods normalizes the specified connection string for acceptance by the SQL data provider.
		/// The connection string for the SQL data provider MUST NOT specify the "Provider" property
		/// (since it is always going to be SQL).
		/// </summary>
		/// <param name="connectionString">Original connection string</param>
		/// <returns>Normalized connection string</returns>
		public static string NormalizeConnectionString(string connectionString)
		{
			return Regex.Replace(connectionString, @"Provider=[^;]+;", "", RegexOptions.IgnoreCase);
		}
		#endregion

		#region Private Methods
		/// <summary>
		/// Compare two column data table for equality.
		/// </summary>
		/// <param name="o1">Data for the first column</param>
		/// <param name="o2">Data for the second column</param>
		/// <returns>True if the data are equal, false otherwise.</returns>
		private static bool ColumnEqual(object o1, object o2)
		{
			if ((o1 == DBNull.Value) && (o2 == DBNull.Value))
				return true; 
			if ((o1 == DBNull.Value) || (o2 == DBNull.Value))
				return false; 
			return o1.Equals(o2);
		}

		/// <summary>
		/// Helper method that handles the Execute* class of ADO.NET methods. Since the bulk of that logic
		/// is identical, it has been factored into this single method.
		/// </summary>
		/// <param name="type">One of ExecuteReader, ExecuteScalar, ExecuteNonQuery</param>
		/// <param name="connectionString">Connection parameters specific to the data provider</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		/// <returns></returns>
		private static object Execute(ExecuteType type, string connectionString, string storedProcedureName, params SqlParameter[] args)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			try
			{
				connection.Open();
				return Execute(type, connection, null, storedProcedureName, args);
			}
			finally
			{
				// We should not close the connection if we are returning a reader! If we did, then the reader
				// would be invalid (and useless to the caller).

				if ((type != ExecuteType.ExecuteReader) && (connection.State != ConnectionState.Closed))
					connection.Close();
			}
		}

		/// <summary>
		/// Helper method that handles the Execute* class of ADO.NET methods. Since the bulk of that logic
		/// is identical, it has been factored into this single method.
		/// </summary>
		/// <param name="type">One of ExecuteReader, ExecuteScalar, ExecuteNonQuery</param>
		/// <param name="connection">An open instance of the SqlConnection object</param>
		/// <param name="transaction">Transaction (if any) associated with the open connection</param>
		/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
		/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
		/// <returns></returns>
		private static object Execute(ExecuteType type, SqlConnection connection, SqlTransaction transaction,
			string storedProcedureName, params SqlParameter[] args)
		{
			if (connection.State != ConnectionState.Open)
				throw new Exception("Connection is not open");

			SqlCommand command = new SqlCommand();
			command.Connection = connection;
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = storedProcedureName;
			command.CommandTimeout = Settings.GetKeyValue("Database.CommandTimeout", 0);
			if (transaction != null)
				command.Transaction = transaction;

			if ((args != null) && (args.Length > 0))
				foreach (SqlParameter p in args)
					command.Parameters.Add(p);
			
			switch (type)
			{
				case ExecuteType.ExecuteNonQuery:
					return command.ExecuteNonQuery();

				case ExecuteType.ExecuteReader:
					return command.ExecuteReader(CommandBehavior.CloseConnection);

				case ExecuteType.ExecuteScalar:
					return command.ExecuteScalar();
			}
			throw new Exception(String.Format("Unsupported ExecuteType '{0}'", type));
		}
		#endregion

		/// <summary>
		/// Select a distinct set of data based on a single column.
		/// </summary>
		/// <param name="dataTable">Source table for the select</param>
		/// <param name="columnName">Column for distinct selection</param>
		/// <param name="includeAllColumns">If true, the returned DataTable contains all columns; else, only the
		/// distinct column</param>
		/// <returns>DataTable containing the distinct selection</returns>
		public static DataTable SelectDistinct(DataTable dataTable, string columnName, bool includeAllColumns)
		{
			DataTable dt = null;
			if (includeAllColumns)
				dt = dataTable.Clone();
			else
			{
				dt = new DataTable();
				dt.Columns.Add(columnName, dataTable.Columns[columnName].DataType);
			}

			object lastValue = null; 
			foreach (DataRow dataRow in dataTable.Select("", columnName))
			{
				if ((lastValue == null) || !ColumnEqual(lastValue, dataRow[columnName]))
				{
					lastValue = dataRow[columnName]; 
					if (includeAllColumns)
						dt.ImportRow(dataRow);
					else
						dt.Rows.Add(new object[]{lastValue});
				}
			}
			return dt;
		}
	}
	#endregion
}

// --- EOF ---

